﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.SqlServer.Server;


namespace WP
{
    using StateList = List<int>;

    public class DB
    {
        public static void AddDB(string DBName)
        {
            
            using (SqlConnection conn = Common.GetConnection())
            {
               
                conn.Open();
                SqlCommand cmd = new SqlCommand();

                //Ellenõrzi, hogy szerepel-e már a trigger a DB-ben
                cmd = Common.InitCmd("wp_CheckDB", conn, CommandType.StoredProcedure);
                cmd.Parameters.AddWithValue("@DBName", DBName);
                SqlDataReader dr = cmd.ExecuteReader();
                dr.Read();
                if (((int)dr[0]) == 1)
                {
                    SqlContext.Pipe.Send("This DB is alredy in the Version Control System.");
                    return;
                }
                dr.Close();

                //Eredeti DB nevének eltárolása
                cmd = Common.InitCmd("select db_name()", conn, CommandType.Text);
                dr = cmd.ExecuteReader();
                string originalDB = "";
                while (dr.Read())
                {
                    originalDB = (string)dr[0];
                }
                dr.Close();

                //DB váltás
                cmd = Common.InitCmd("USE " + DBName.ToString(), conn, CommandType.Text);
                cmd.ExecuteNonQuery();

                //A verziókövetéshez szükséges objektumok létrehozása a céladatbázison
                cmd = Common.InitCmd(Scripts.wp_GetText080404, conn, CommandType.Text);
                cmd.ExecuteNonQuery();
                cmd = Common.InitCmd(Scripts.wp_logger080407, conn, CommandType.Text);
                cmd.ExecuteNonQuery();

                //Az SP-k lekérdezése
                cmd = Common.InitCmd("select name from sysobjects where xtype = 'P' order by name", conn, CommandType.Text);
                dr = cmd.ExecuteReader();
                List<string> procnames = new List<string>();
                Dictionary<string, string> procedures = new Dictionary<string, string>();
                while (dr.Read())
                {
                    procnames.Add((string)dr["name"]);
                }
                dr.Close();

                //Az SP-k tartalmának lekérdezése
                cmd = Common.InitCmd("sp_helptext", conn, CommandType.StoredProcedure);
                cmd.Parameters.Add("@objname", SqlDbType.VarChar, 776);
                string temp;
                string temp2;
                foreach (string item in procnames)
                {
                    temp = "";
                    cmd.Parameters[0].Value = item;
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        temp = temp + (string)dr["Text"];
                        temp2 = temp;
                        if (temp2.ToLower().Contains("create proc"))
                        {
                            int index = temp2.ToLower().IndexOf("create proc");
                            temp = temp.Remove(index, 11);
                            temp = temp.Insert(index, "alter proc");
                        }
                    }
                    dr.Close();
                    procedures.Add(item, temp.Trim());
                }

                //Az SP-k lefuttatása
                foreach (KeyValuePair<string, string> item in procedures)
                {
                    cmd = Common.InitCmd(item.Value, conn, CommandType.Text);
                    cmd.ExecuteNonQuery();
                }

                //A DB visszaállítása
                cmd = Common.InitCmd("USE " + originalDB, conn, CommandType.Text);
                cmd.ExecuteNonQuery();

                //DataTable tblProcedures = conn.GetSchema(SqlClientMetaDataCollectionNames.Procedures, new string[] { DBName.ToString(), null, null, "PROCEDURE" });
                //foreach (DataRow rowProcedures in tblProcedures.Rows)
                //{
                //SqlContext.Pipe.Send(rowProcedures["specific_name"].ToString());
                //}
            }
        }

        /// <summary>
        /// Brings database to the given state
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="to"></param>
        public static void ConvertDB(string dbName, State to)
        {

        }

        public static State GetCurrentState(string dbName)
        {
            return 0;
        }

        
        public static String GetScript(StateList list)
        {
            string result = "";
            foreach (int item in list)
            {
                result +=(new State(item)).Script + Common.SQLBatchSeparator;
            }
            return result;
        }


        
        /// <summary>
        /// A megadott branch alapján a megfelelõ adatbázis objektumainak állapotlistáját adja vissza
        /// </summary>
        /// <param name="branch">A branch neve</param>
        /// <returns>Az állapotlista</returns>
        public static StateList GetBranchStates(string branch)
        {
            StateList returnvalue = new StateList();
            using (SqlConnection conn = Common.GetConnection())
            {
                conn.Open();
                using (SqlCommand cmd = Common.InitCmd("wp_GetBranchStates", conn, CommandType.StoredProcedure))
                {
                    cmd.Parameters.AddWithValue("@branch", branch);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            returnvalue.Add(rdr.GetInt32(0));
                        }
                    }
                }
            }
            return returnvalue;
        }

        /// <summary>
        /// A megadott tag alapján a megfelelõ adatbázis objektumainak állapotlistáját adja vissza
        /// </summary>
        /// <param name="tag">A branch neve</param>
        /// <returns>Az állapotlista</returns>
        public static StateList GetTagStates(string tag)
        {
            StateList returnvalue = new StateList();
            using (SqlConnection conn = Common.GetConnection())
            {
                conn.Open();
                using (SqlCommand cmd = Common.InitCmd("wp_GetTagStates", conn, CommandType.StoredProcedure))
                {
                    cmd.Parameters.AddWithValue("@tag", tag);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            returnvalue.Add(rdr.GetInt32(0));
                        }
                    }
                }
            }
            return returnvalue;
        }



        /// <summary>
        ///  A megadott state alapján a megfelelõ adatbázis objektumainak állapotlistáját adja vissza
        /// </summary>
        /// <param name="state">A state</param>
        /// <returns>Az állapotlista</returns>
        public static StateList GetStateStates(int state)
        {
            StateList returnvalue = new StateList();
            using (SqlConnection conn = WP.Common.GetConnection())
            {
                conn.Open();
                using (SqlCommand cmd = Common.InitCmd("wp_GetStateStates", conn, CommandType.StoredProcedure))
                {
                    cmd.Parameters.AddWithValue("@state", state);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            returnvalue.Add(rdr.GetInt32(0));
                        }
                    }
                }
            }
            return returnvalue;
        }

        /// <summary>
        /// Az adatbázis objektumainak a megadott dátumhoz mért legfrissebb állapotlistáját adja vissza.
        /// Ha a dátum null, akkor az adatbázis aktuális állapotlistájával tér vissza.
        /// </summary>
        /// <param name="dbname">Az adatbázis neve</param>
        /// <param name="date">Dátum</param>
        /// <returns>Az állapotlista</returns>
        public static StateList GetDateStates(string dbname, DateTime date)
        {
            StateList returnvalue = new StateList();
            using (SqlConnection conn = WP.Common.GetConnection())
            {
                conn.Open();
                using (SqlCommand cmd = Common.InitCmd("wp_GetDateStates", conn, CommandType.StoredProcedure))
                {
                    cmd.Parameters.AddWithValue("@DBname", dbname);
                    cmd.Parameters.AddWithValue("@date", date);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            returnvalue.Add(rdr.GetInt32(0));
                        }
                    }
                }
            }
            return returnvalue;
        }

        /// <summary>
        /// A legutoljára módosított adatbázis legfrissebb állapotlistájával tér vissza
        /// </summary>
        /// <returns>Az állapotlista</returns>
        public static StateList GetLastStates()
        {
            StateList returnvalue = new StateList();
            using (SqlConnection conn = WP.Common.GetConnection())
            {
                conn.Open();
                using (SqlCommand cmd = Common.InitCmd("wp_GetLastStates", conn, CommandType.StoredProcedure))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            returnvalue.Add(rdr.GetInt32(0));
                        }
                    }
                }
            }
            return returnvalue;
        }

        public static StateList GetStates(string name, ParamType type)
        {
            StateList states = new StateList();
            if (type == ParamType.Branch)
            {
                states = GetBranchStates(name);
            }
            else
            {
                states = GetTagStates(name);
            }
            return states;
        }

        public static StateList GetStates(DateTime date, string DBname)
        {
            StateList states = new StateList();
            states = GetDateStates(DBname, date);
            return states;
        }

        public static StateList GetStates(int state)
        {
            StateList states = new StateList();
            states = GetStateStates(state);
            return states;
        }

        public static StateList GetStates(string DBname)
        {
            StateList states = new StateList();
            states = GetLastStates();
            return states;
        }

        public enum ParamType
        {
            Branch = 1,
            Tag = 2
        }
    }
}
